Framing the Problem

  • BEB’s management team is highly experienced from a venture capital standpoint.

  • BEB hired you as a recent MBA graduate reporting to the CFO.

  • The CFO is a great leader and understands very well that she needs your skills in helping enroll other Executives as they move towards managing growth in a financially sound manner.

  • They hired you based on your MBA designation with the following expectations that you:

    • Demonstrate a well-rounded knowledge base.

    • Have solids skills in business communication and critical thinking that goes beyond business common sense which they already have.

    • Effectively draw from your Finance knowledge to educate members of the Board’s Risk Management Committee. BEB practices decision quality.

  • Your first assignment from the CFO is to write a business document for the Risk Committee pertaining to concepts that are related to risk-managing their exposure to the cost of diesel. With the wild ride in diesel prices as a result of events in 2022, the Board is concerned about the cost of transportation from their warehouses in New York and their nationwide locations.

Skills Practiced & Requirements

  • Use of APIs for Enterprise-ready skill in working efficiently:

    • EIA.
    • Morningstar Commodities.
    • Strictly no Excel, csv or use of other local files in any work you do.
  • Data wrangling and parsing. You must use tidy data with long and wide data frames.

  • Quality presentation skills with effective choice of visualization in communication - after all, the ability to tell a story and enroll leaders is what they expect from an MBA.

  • Foundational risk management skills.

  • Clean, clear and concise rendering of a business document. You may refer to the rmarkdown, quarto resources or any other online document to customize your output. I reserve the right to award a maximum of 50% for any document that is not worthy of a proper business communication. For clarity, the metric I use is simple: if I feel I could not press the forward button in Outlook it’s short on quality. This is not a technical rendering of what you did to your Professor - I can read code in your .Rmd document if I need to but I don’t expect to see it in a business document. Some characteristics of a properly presented document:

    • A clear heading with title and who it is from.
    • Cleaning organized by business questions asked.
    • Charts and tables properly formatted and self explanatory.
  • Each question should be answered as if you were presenting to BEB’s management team.

  • “Learn to learn” is a core objective of higher education: Using new packages and functions as you see fit to achieve your desired outcome.

Whilst there are many packages to format tables the following are suggested:

Data

Use the following data from 2022-01-01 to 2022-12-31 inclusive:

  • Spot price is from the EIA: New York Harbor Ultra-Low Sulfur No 2 Diesel Spot Price, Daily. You may refer to the bootcamp appendix on APIs after you have registered and obtained your own API key. The RTL packages provide functions to access the API once you have your API key.
  • Extract historical settlement prices for the December 2022 CME NY Harbor ULSD futures contract form Morningstar Commodities for futures contracts using RTL::getPrices() and your credentials. This is covered in the Data Wrangling in Tidyverse module of the course.

You will need to merge the two resulting dataframes. To join, or the equivalent of a VLOOKUP in Excel, you can use dplyr::left_join():

  • ?dplyr::left_join
  • In this example, the left dataframe is x and the right dataframe is y and we join them by the variable date.
x <- dplyr::tibble(date = as.Date("2022-01-01"), a = 10)
y <- dplyr::tibble(date = as.Date("2022-01-01"), b = 20)
x %>% dplyr::left_join(y, by = c("date"))
## # A tibble: 1 × 3
##   date           a     b
##   <date>     <dbl> <dbl>
## 1 2022-01-01    10    20

Your Ask

  • Using Rmarkdown or quarto, render into a clean and concise html document.
  • Name your file firstNameLastName.Rmd and send it to pcote@ualberta.ca before the deadline.
  • This is strictly an individual exercise. Any attempt to communicate or cooperate with any other person will be deemed a violation of Academic Integrity and Honesty. For clarity this includes any online chat platforms (e.g. Discord) where I or the UoA might find evidence of communication in a formal investigation.
  • Grading is in the class note Grading module and any late submission will be assigned a grade of 0% - no questions asked.

Part A: Spot and Futures Contracts: What are they?

All the Board understands is that BEB is purchasing diesel directly from a wholesaler for its truck fleet at whatever price is offered on that day. You, however, know that you can use other instruments to manage the price risk. You will be using the specific spot and futures contracts detailed in the data section for this memo.

Explain in a paragraph or two:

  • What are spot and futures prices?
  • The important aspects of the futures contract specifications and how they related to the context provided on BEB’s operations.

Part B: Spot versus futures prices: Risk

  • Using relative returns (\(\frac{price_t}{price_{t-1}}-1\)) compute the daily risk of the respective spot and futures contracts.
  • Build a time series chart of the the spot price and futures price data.

With the above information nicely presented, tell the story of what the Committee key takeaways should be on what happened in 2022. BEB is purchasing spot and could potentially hedge with futures contracts.

series risk
HO22Z 7.68%
Spot 18.53%